package edu.byu.isys413.mystuff;
/*
 * customerDAO.java
 * Author : abasnet
 *
 * Created on February 15, 2007, 8:31 PM
 *
 * To change this template, choose Tools | Template Manager
 * and open the template in the editor.
 */

import java.util.*;
import java.sql.*;

public class customerDAO {
    private static customerDAO instance;
    private String custID;
    private static Cache cache;
    /** Creates a new instance of customerDAO */
    public customerDAO() {
    cache = Cache.getInstance();
    }
    
    public static synchronized customerDAO getInstance() {
        if (instance == null) {
            instance = new customerDAO();
            
        }
        return instance;
    }
    
    public customerBO create(){
        custID = Guid.generate();
        customerBO custBO = new customerBO(custID);
        cache.put(custID,custBO);
        return custBO;
    }
    
    /*
     *This method reads the record from the database  and loads the Customer Business object
     * @parameter : the id of the record
     * @ returns : the customerBO, loaded with the data from the database
     */
    public synchronized ArrayList<customerBO> readALL() throws DataException {
        ArrayList<customerBO> custs = new ArrayList();
        Connection conn = null;
        try {
            conn = ConnectionPool.getInstance().get();
            
            Statement st = conn.createStatement();
            ResultSet rs = st.executeQuery("SELECT * FROM CUSTOMER_TBL");
            
            while(rs.next()){
                customerBO cusbo = new customerBO(rs.getString("CUST_ID"));
                cusbo.setFirstName(rs.getString("FIRST_NAME"));//artist
                cusbo.setLasttName(rs.getString("LAST_NAME"));//album
                cusbo.setEmail(rs.getString("EMAIL"));//email Address
                cusbo.setPhone(rs.getString("PHONE"));//phone
                cusbo.setAddress1(rs.getString("ADDRESS1"));//city
                cusbo.setAddress2(rs.getString("ADDRESS2"));//state
                cusbo.setCity(rs.getString("CITY"));
                cusbo.setState(rs.getString("STATE"));
                cusbo.setZipcode(rs.getString("ZIPCODE"));
                custs.add(cusbo);
            }
            ///////////////////////////////////////////
            // call read with a connection (the other read method in this class)
            
            // release the connection
            conn.commit();
            ConnectionPool.getInstance().release(conn);
            
        }catch (ConnectionPoolException e){
            throw new DataException("Could not get a connection to the database.");
            
        }catch (SQLException e) {
            // rollback
            try {
                conn.rollback();
                ConnectionPool.getInstance().release(conn);
            }catch (SQLException e2) {
                throw new DataException("Big error: could not even release the connection", e2);
            }catch (ConnectionPoolException e3){
            }
            
            throw new DataException("Could not retrieve records", e);
        }
        
        return custs;
    }
    
    public synchronized String findCustomer(String name, String email)throws DataException{
        String custID = "";
        String firstName = "";
        String lastName = "";
        if(name.length()!=0){
            String[] results = name.split("\\s");
            firstName = results[0];
            lastName = results[results.length-1];
        }
        
        Connection conn = null;
        try {
            conn = ConnectionPool.getInstance().get();
            
            Statement st = conn.createStatement();
            String sqlstring = "SELECT CUST_ID FROM CUSTOMER_TBL WHERE FIRST_NAME='"+firstName+"' OR LAST_NAME='"+lastName+"' AND EMAIL = '"+email+"'";
            ResultSet rs = st.executeQuery(sqlstring);
            
            while(rs.next()){
                custID = rs.getString("CUST_ID");
            }
            ///////////////////////////////////////////
            // call read with a connection (the other read method in this class)
            
            // release the connection
            conn.commit();
            ConnectionPool.getInstance().release(conn);
            
        }catch (ConnectionPoolException e){
            throw new DataException("Could not get a connection to the database.");
            
        }catch (SQLException e) {
            // rollback
            try {
                conn.rollback();
                ConnectionPool.getInstance().release(conn);
            }catch (SQLException e2) {
                throw new DataException("Big error: could not even release the connection", e2);
            }catch (ConnectionPoolException e3){
            }
            
            throw new DataException("Could not retrieve records", e);
        }
        
        return custID;
    }
    
    public synchronized customerBO read(String id) throws DataException{
        customerBO cusbo = null;
        Connection conn = null;
        try {
            conn = ConnectionPool.getInstance().get();
            cusbo = read(conn,id);
            
            ///////////////////////////////////////////
            // call read with a connection (the other read method in this class)
            
            // release the connection
            conn.commit();
            ConnectionPool.getInstance().release(conn);
            
        }catch (ConnectionPoolException e){
            throw new DataException("Could not get a connection to the database.");
            
        }catch (SQLException e) {
            // rollback
            try {
                conn.rollback();
                ConnectionPool.getInstance().release(conn);
            }catch (SQLException e2) {
                throw new DataException("Big error: could not even release the connection", e2);
            }catch (ConnectionPoolException e3){
            }
            
            throw new DataException("Could not retrieve records", e);
        }
        
        return cusbo;
    }
    
    public synchronized customerBO read(Connection conn, String id){
       customerBO cusbo = null;
        try{
        Statement st = conn.createStatement();
            ResultSet rs = st.executeQuery("SELECT * FROM CUSTOMER_TBL WHERE CUST_ID='"+id+"'");
            cusbo = new customerBO(id);
            while(rs.next()){
                
                cusbo.setFirstName(rs.getString("FIRST_NAME"));//artist
                cusbo.setLasttName(rs.getString("LAST_NAME"));//album
                cusbo.setEmail(rs.getString("EMAIL"));//email Address
                cusbo.setPhone(rs.getString("PHONE"));//phone
                cusbo.setAddress1(rs.getString("ADDRESS1"));//city
                cusbo.setAddress2(rs.getString("ADDRESS2"));//state
                cusbo.setCity(rs.getString("CITY"));
                cusbo.setState(rs.getString("STATE"));
                cusbo.setZipcode(rs.getString("ZIPCODE"));
                
            }
       }catch(Exception e){}
       return cusbo;
    }
    ///////////////update//////////////////
    
    /////////////save////////////////////
    public synchronized boolean save(customerBO cusbo) throws DataException {
       
        Connection conn = null;
        try {
            
            conn = ConnectionPool.getInstance().get();
            save(conn,cusbo);
            
            ///////////////////////////////////////////
            // call read with a connection (the other read method in this class)
            
            // release the connection
            conn.commit();
            ConnectionPool.getInstance().release(conn);
            
        }catch (ConnectionPoolException e){
            throw new DataException("Could not get a connection to the database.");
            
        }catch (SQLException e) {
            // rollback
            try {
                conn.rollback();
                ConnectionPool.getInstance().release(conn);
            }catch (SQLException e2) {
                throw new DataException("Big error: could not even release the connection", e2);
            }catch (ConnectionPoolException e3){
            }
            
            throw new DataException("Could not retrieve records", e);
        }
        
        return true;
        }
    
    public synchronized void save(Connection conn, customerBO cusbo){
        try{
            Statement st = conn.createStatement();
            ResultSet rs = st.executeQuery("SELECT * FROM CUSTOMER_TBL WHERE CUST_ID ='"+cusbo.getId()+"'");
            String fname = "";
            while(rs.next())
                fname = rs.getString("FIRST_NAME");
           
            
            if(fname == ""){
                
                String queryString = "INSERT INTO CUSTOMER_TBL VALUES (?,?,?,?,?,?,?,?,?,?)";
                PreparedStatement pstmt = conn.prepareStatement(queryString);
               
                pstmt.setString(1,cusbo.getId());
                pstmt.setString(2,cusbo.getFirstName());
                pstmt.setString(3,cusbo.getLasttName());
                pstmt.setString(4,cusbo.getEmail());
                pstmt.setString(5,cusbo.getPhone());
                pstmt.setString(6,cusbo.getAddress1());
                pstmt.setString(7,cusbo.getAddress2());
                pstmt.setString(8,cusbo.getCity());
                pstmt.setString(9,cusbo.getState());
                pstmt.setString(10,cusbo.getZipcode());
                pstmt.execute();
                pstmt.close();
            } else{
            
                String queryString = "UPDATE CUSTOMER_TBL SET FIRST_NAME = ?,LAST_NAME = ? , EMAIL = ?, PHONE = ?, ADDRESS1 = ?, ADDRESS2 = ?, CITY = ?, STATE = ?, ZIPCODE = ? WHERE CUST_ID = ? ";
                PreparedStatement pstmt = conn.prepareStatement(queryString);
                System.out.println(cusbo);
                pstmt.setString(1,cusbo.getFirstName());//name
                pstmt.setString(2,cusbo.getLasttName());
                pstmt.setString(3,cusbo.getEmail());
                pstmt.setString(4,cusbo.getPhone());
                pstmt.setString(5,cusbo.getAddress1());
                pstmt.setString(6,cusbo.getAddress2());
                pstmt.setString(7,cusbo.getCity());
                pstmt.setString(8,cusbo.getState());
                pstmt.setString(9,cusbo.getZipcode());
                pstmt.setString(10,cusbo.getId());
                pstmt.execute();
                pstmt.close();
                
            }
        }catch(Exception e){}
    }
    
    /////////////delete////////////////////////
    //explain why we are not deleting
    /*
    public static void main(String[] args){
    customerDAO cusdao =  customerDAO.getInstance();
        try{
        cusdao.read("asdfasd");
        }catch(DataException e){}
    }
     */
    
}
